# -*- encoding:utf-8 -*-
"""验证APP"""
import StringIO
import base64
import json
import time
import requests
import pickle
from datetime import datetime

from flask import Blueprint, g, request, session, Response
from psycopg2 import connect
from psycopg2.extras import RealDictCursor

from config import config
from lib import AppSession, funcs, auth, record
from lib.table import table
from lib.vaildcode import ImageChar
from lib.validform import V, ValidateForm

app = Blueprint(__name__ + "_app", __name__)


@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


@app.route('/api/validcode')
def get_validcode():
    # 把strs发给前端,或者在后台使用session保存
    valid = ImageChar(fontColor=(100, 211, 90))
    strs, code_img = valid.randChinese(4)
    buf = StringIO.StringIO()
    code_img.save(buf, 'JPEG', quality=70)
    buf_str = buf.getvalue()
    validcode = base64.b64encode(buf_str)
    response = Response(
        json.dumps({
            "status": "success",
            "data": {
                "image": validcode
            }
        }))
    response.set_cookie('validcode', strs)
    return response


@app.route('/api/auth', methods=['GET'])
@app.route('/api/auth/<token>', methods=['GET'])
def get_authinfo(token=''):
    """查询权限
    -  不使用Token可以查询当前用户的信息
    -  可以在请求头中包含token  -  也可以在请求参数中包含token  -
    -  如果token无效,返回{}     -  token有效则返回用户信息
    """
    try:
        token = token if token else request.values.get('token', '')
        token = token if token else request.cookies.get('token', '')
        if token:
            g.cursor.execute("select userinfo from sys_session where token=%s",
                             (token, ))
            userinfo = g.cursor.fetchone()
            if userinfo:
                userinfo = AppSession.SessionObj(
                    pickle.loads(userinfo['userinfo']))['_currUser']
                g.cursor.execute(
                    """update sys_session set r_time=%s where token=%s""",
                    (datetime.now(), token))
                g.conn.commit()
        else:
            userinfo = session.get("_currUser", {})

        if not userinfo or userinfo == {}:
            return json.dumps({"status": "timeout"})
        else:
            # 验证是否是初始用户
            g.cursor.execute(
                """select user_password as password from sys_login where user_name = %s and state = '1'""",
                (userinfo['user']['user_name'], ))
            if g.cursor.fetchone()['password'] == funcs.calc_md5(
                    config.defaultPassword):
                return json.dumps({"status": "default", "msg": "修改初始密码"})
            else:
                return json.dumps({"status": "success", "data": userinfo})
    except Exception, e:
        return json.dumps({"status": "fail", "msg": "查询失败"})


@app.route('/api/auth/sso', methods=['POST'])
def add_authinfo_sso():
    """通过单点登录方式 登录平台"""
    form = ValidateForm(
        code=['登录码', V.required()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)
    try:
        # 根据用户的code获取用户信息
        user_data = requests.get(config.SSO_URL + data["code"]).json()
        if user_data["error"] != 0:
            return json.dumps({"status": "fail", "msg": "用户登录失败"})

        # 判断账号的正确性
        g.cursor.execute(
            """select user_name, repeat,d2t(login_time) as login_time from sys_login where user_name = %s and state = '1'""",
            (user_data['username'], ))
        account = g.cursor.fetchone()

        # 账号正确
        if account:
            data['username'] = account["user_name"]
            # 登录状态判定
            if config.authStatus == 'keep':
                pass
            elif config.authStatus == 'limit':
                # 获取IP地址
                loginIP = request.headers.get(
                    config.ipType, None) if request.headers.get(
                        config.ipType, None) else request.remote_addr
                g.cursor.execute(
                    "select count(1) as num from sys_session where user_name = %s and ip <> %s",
                    (data['username'], loginIP))
                if g.cursor.fetchone()['num'] > 0:
                    return json.dumps({
                        "status": "fail",
                        "msg": "账号已在其它地方登陆, 登录IP为：%s" % loginIP
                    })
            elif config.authStatus == 'hoof':
                g.cursor.execute(
                    "delete from sys_session where user_name = %s",
                    (data['username'], ))
                g.conn.commit()

            g.cursor.execute(
                """update sys_login set repeat = 0,login_time = %s where user_name = %s""",
                (datetime.now(), data['username']))
            g.conn.commit()
            # 获取用户信息 $$
            userData = {}
            g.cursor.execute(
                """select a.person_id, b.login_id, b.user_name, c.agency_id As agency_id,
                                                c.agency_name  as department_name,a.person_name,a.sex,
                                                to_char(a.birthday, 'YYYY-MM-DD') as birthday,a.email,a.mobile_phone,a.remark, string_agg(e.user_group_name,',') as user_group_name
                                from sys_person a
                                left join sys_login b on a.person_id = b.person_id
                                left join sys_agency_info c on a.agency_id = c.agency_id
                                left join sys_login_group d on b.login_id = d.login_id
                                left join sys_user_group e on d.user_group_id = e.user_group_id
                                where a.state = '1' and b.state = '1' and user_name = %s
                                group by a.person_id, b.login_id, b.user_name, c.agency_id,
                                c.agency_name,a.person_name,a.sex, to_char(a.birthday, 'YYYY-MM-DD'),a.email,a.mobile_phone,a.remark""", (data['username'], ))
            userData['user'] = g.cursor.fetchone()
            # 获取用户信息
            g.cursor.execute(
                """select d2t(operate_time) as time from sys_operate_log
                    where login_id = %s and operate_type='登录'
                    order by operate_time desc limit 1""",
                (userData['user']['login_id'], ))
            lastLoginTime = g.cursor.fetchone()
            userData['user']['lastLoginTime'] = lastLoginTime[
                'time'] if lastLoginTime else '这是您首次登陆本系统'

            # 获取菜单结构
            g.cursor.execute(
                """select distinct d.group_id, d.cn_name as text, d.group_key as key, d.icon, d.path, d.level, d.father_id, d.step
                                from sys_usergroup_role a
                                left join sys_role b on a.role_id = b.role_id
                                left join sys_role_function c on b.role_id = c.role_id
                                left join sys_function_group d on c.group_id = d.group_id
                                where a.user_group_id in (select a.user_group_id 
                                                          from sys_login_group a
                                                          left join sys_user_group b on a.user_group_id = b.user_group_id
                                                          where login_id = %s and b.state = '1'
                                                        ) and d.level = 1 and d.state='1' order by step""",
                (userData['user']['login_id'], ))
            userData['menu'] = g.cursor.fetchall()
            
            # 判断用户是否在用户组中
            if len(userData['menu']) == 0:
                return json.dumps(
                    {
                        "status": "fail",
                        "msg": "用户未被分配权限"
                    }
                )
            for i in userData['menu']:
                if not i['path']:
                    g.cursor.execute(
                        """select distinct d.group_id, d.cn_name as text, d.group_key as key, d.icon, d.path, d.level, d.father_id, d.step
                                            from sys_usergroup_role a
                                            left join sys_role b on a.role_id = b.role_id
                                            left join sys_role_function c on b.role_id = c.role_id
                                            left join sys_function_group d on c.group_id = d.group_id
                                        where a.user_group_id in (select user_group_id 
                                                                  from sys_login_group 
                                                                  where login_id = %s
                                                                ) and d.state = '1' and d.level = 2 and father_id = %s order by step""",
                        (userData['user']['login_id'], i['group_id']))
                    i['children'] = g.cursor.fetchall()

                    # 增加三级菜单(临时)
                    for child in i['children']:
                        if not child['path']:
                            g.cursor.execute(
                                """select distinct d.group_id, d.cn_name as text, d.group_key as key, d.icon, d.path, d.level, d.father_id, d.step
                                                    from sys_usergroup_role a
                                                    left join sys_role b on a.role_id = b.role_id
                                                    left join sys_role_function c on b.role_id = c.role_id
                                                    left join sys_function_group d on c.group_id = d.group_id
                                                where a.user_group_id in (select user_group_id 
                                                                          from sys_login_group 
                                                                          where login_id = %s
                                                                        ) and d.state = '1' and d.level = 3 and father_id = %s order by step""",
                                (userData['user']['login_id'], child['group_id']))
                            child['children'] = g.cursor.fetchall()
            if len(userData['menu'])>0:  
                for item in userData['menu']:
                    if item['key']!='view':
                        if (item['level'] != 1) or (item.has_key('children') and len(item['children'])==0) or (not item.has_key('children')):
                            print item
                            return json.dumps(
                                {
                                    "status": "fail",
                                    "msg": "用户权限分配有误"
                                }
                            )
            # 获取权限信息
            g.cursor.execute(
                """select distinct c.read_only_state as is_readonly, d.group_key
                                from sys_usergroup_role a
                                left join sys_role b on a.role_id = b.role_id
                                left join sys_role_function c on c.role_id = b.role_id
                                left join sys_function_group d on d.group_id = c.group_id
                                where a.user_group_id in (select user_group_id 
                                                          from sys_login_group 
                                                          where login_id = %s
                                                        ) and b.state = '1' and d.state = '1'""", (userData['user']['login_id'],))

            userData['readOnly'] = set()
            userData['function'] = set()

            for i in g.cursor.fetchall():
                if int(i['is_readonly']) == 0:
                    userData['function'].add(i['group_key'])
                userData['readOnly'].add(i['group_key'])

            # 特殊权限处理
            if "warnings" in userData["function"]:
                userData["function"].add("important")
                userData["function"].add("website")
                userData["function"].add("dailytask")
            if "unit" in userData["function"]:
                userData["function"].add("safeEvents")

            # 消重
            userData['readOnly'] = list(userData['readOnly'])
            userData['function'] = list(userData['function'])

            # 保存登录信息
            userData['token'] = session.sid
            userData['project'] = {"name": config.PROJECT_NAME}
            session['_currUser'] = userData
            # 记录日志信息
            auth.logsync(userData, {
                "function": "权限系统",
                "type": "登录",
                "remark": "登录系统"
            })
            return json.dumps({"status": "success", "data": userData})
        else:
            return json.dumps({"status": "fail", "msg": "用户名或密码不正确"})
    except Exception, e:
        return json.dumps({"status": "fail", "msg": "用户登录失败"})


@app.route('/api/auth', methods=['POST'])
def add_authinfo():
    """用户登录"""
    form = ValidateForm(
        username=[
            '账号',
            V.required(type='base64'),
            V.rangelength(3, 20),
            V.pattern(r'^[a-zA-Z0-9_]+$', '由字母数字下划线组成')
        ],
        password=[
            '密码',
            V.required(type='base64'),
            V.rangelength(6, 20),
            V.pattern(r'^[a-zA-Z0-9_~`!@#\$%\^&\*\(\)\-\+,\.]+$',
                      '由字母、数字、符号(_~`!@#$%^&*()-+,.)组成')
        ],
        validcode=['验证码', V.required(type='base64')])
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)
    try:
        import time
        time.sleep(1)
        # 验证验证码的正确性
        if request.cookies['validcode'] != data['validcode']:
            return json.dumps({"status": "fail", "msg": "验证码不正确"})
        # 判断账号的正确性
        g.cursor.execute(
            """select repeat,d2t(login_time) as login_time from sys_login where user_name = %s and state = '1'""",
            (data['username'], ))
        account = g.cursor.fetchone()
        # 账号正确
        if account:
            # 登录状态判定
            if config.authStatus == 'keep':
                pass
            elif config.authStatus == 'limit':
                # 获取IP地址
                loginIP = request.headers.get(
                    config.ipType, None) if request.headers.get(
                        config.ipType, None) else request.remote_addr
                g.cursor.execute(
                    "select count(1) as num from sys_session where user_name = %s and ip <> %s",
                    (data['username'], loginIP))
                if g.cursor.fetchone()['num'] > 0:
                    return json.dumps({
                        "status": "fail",
                        "msg": "账号已在其它地方登陆, 登录IP为：%s" % loginIP
                    })
            elif config.authStatus == 'hoof':
                g.cursor.execute(
                    "delete from sys_session where user_name = %s",
                    (data['username'], ))
                g.conn.commit()

            # 登录验证开始 $$
            if account['repeat'] >= 3 and (datetime.now() - datetime.strptime(
                    account['login_time'],
                    "%Y-%m-%d %H:%M:%S")).seconds <= config.sessionSaveTime:
                g.cursor.execute(
                    """select login_id from sys_login WHERE user_name = %s""",
                    (data['username'], ))
                login_id = g.cursor.fetchone()['login_id']

                g.cursor.execute("""INSERT INTO public.sys_operate_log(
                                            login_id, operate_fun, operate_type, remark,
                                            operate_time)
                                    VALUES ('%s', '权限系统', '登录', '登录系统', now());"""
                                 % login_id)
                g.conn.commit()
                return json.dumps({"status": "fail", "msg": "账号已被锁定，请稍后再试"})
            else:
                # 验证密码的正确性
                g.cursor.execute(
                    """select count(1) as num from sys_login where user_name = %s and user_password = %s and state = '1'""",
                    (data['username'], funcs.calc_md5(data['password'])))
                if g.cursor.fetchone()['num'] == 0:
                    if (datetime.now() - datetime.strptime(
                            account['login_time'], "%Y-%m-%d %H:%M:%S")).seconds <= config.sessionSaveTime:
                        # 如果在规定时间内连续输错
                        g.cursor.execute(
                            """update sys_login set repeat = %s where user_name = %s""",
                            (int(account['repeat']) + 1, data['username']))
                    else:
                        g.cursor.execute(
                            """update sys_login set repeat = 1,login_time = %s where user_name = %s""",
                            (datetime.now(), data['username']))
                    g.conn.commit()
                    return json.dumps({
                        "status":
                        "fail",
                        "msg":
                        "用户名或密码不正确，您还有%s次机会输入" %
                        (3 - int(account['repeat']) - 1)
                    })
                else:
                    g.cursor.execute(
                        """update sys_login set repeat = 0,login_time = %s where user_name = %s""",
                        (datetime.now(), data['username']))
                    g.conn.commit()
                    # 获取用户信息 $$
                    userData = {}
                    g.cursor.execute(
                """select a.person_id, b.login_id, b.user_name, c.agency_id As agency_id,
                                                c.agency_name  as department_name,a.person_name,a.sex,
                                                to_char(a.birthday, 'YYYY-MM-DD') as birthday,a.email,a.mobile_phone,a.remark, string_agg(e.user_group_name,',') as user_group_name
                                from sys_person a
                                left join sys_login b on a.person_id = b.person_id
                                left join sys_agency_info c on a.agency_id = c.agency_id
                                left join sys_login_group d on b.login_id = d.login_id
                                left join sys_user_group e on d.user_group_id = e.user_group_id
                                where a.state = '1' and b.state = '1' and user_name = %s
                                group by a.person_id, b.login_id, b.user_name, c.agency_id,
                                c.agency_name,a.person_name,a.sex, to_char(a.birthday, 'YYYY-MM-DD'),a.email,a.mobile_phone,a.remark""",
                        (data['username'], ))
                    userData['user'] = g.cursor.fetchone()
                    # 获取用户信息
                    g.cursor.execute(
                        """select d2t(operate_time) as time from sys_operate_log
                            where login_id = %s and operate_type='登录'
                            order by operate_time desc limit 1""",
                        (userData['user']['login_id'], ))
                    lastLoginTime = g.cursor.fetchone()
                    userData['user']['lastLoginTime'] = lastLoginTime[
                        'time'] if lastLoginTime else '这是您首次登陆本系统'

                    # 获取菜单结构
                    g.cursor.execute(
                        """select distinct d.group_id, d.cn_name as text, d.group_key as key, d.icon, d.path, d.level, d.father_id, d.step
                                        from sys_usergroup_role a
                                        left join sys_role b on a.role_id = b.role_id
                                        left join sys_role_function c on b.role_id = c.role_id
                                        left join sys_function_group d on c.group_id = d.group_id
                                        where a.user_group_id in (select a.user_group_id 
                                                                  from sys_login_group a
                                                                  left join sys_user_group b on a.user_group_id = b.user_group_id
                                                                  where login_id = %s and b.state = '1'
                                                                ) and d.level = 1 and d.state='1' order by step""",
                        (userData['user']['login_id'], ))
                    userData['menu'] = g.cursor.fetchall()

                    # 判断用户是否在用户组中
                    if len(userData['menu']) == 0:
                        return json.dumps(
                            {
                                "status": "fail",
                                "msg": "用户未被分配权限"
                            }
                        )
                    
                    for i in userData['menu']:
                        if not i['path']:
                            g.cursor.execute(
                                """select distinct d.group_id, d.cn_name as text, d.group_key as key, d.icon, d.path, d.level, d.father_id, d.step
                                                    from sys_usergroup_role a
                                                    left join sys_role b on a.role_id = b.role_id
                                                    left join sys_role_function c on b.role_id = c.role_id
                                                    left join sys_function_group d on c.group_id = d.group_id
                                                where a.user_group_id in (select user_group_id 
                                                                        from sys_login_group 
                                                                        where login_id = %s
                                                                        ) and d.state = '1' and d.level = 2 and father_id = %s order by step""",
                                (userData['user']['login_id'], i['group_id']))
                            i['children'] = g.cursor.fetchall()

                            # 增加三级菜单(临时)
                            for child in i['children']:
                                if not child['path']:
                                    g.cursor.execute(
                                        """select distinct d.group_id, d.cn_name as text, d.group_key as key, d.icon, d.path, d.level, d.father_id, d.step
                                                            from sys_usergroup_role a
                                                            left join sys_role b on a.role_id = b.role_id
                                                            left join sys_role_function c on b.role_id = c.role_id
                                                            left join sys_function_group d on c.group_id = d.group_id
                                                        where a.user_group_id in (select user_group_id 
                                                                                from sys_login_group 
                                                                                where login_id = %s
                                                                                ) and d.state = '1' and d.level = 3 and father_id = %s order by step""",
                                        (userData['user']['login_id'], child['group_id']))
                                    child['children'] = g.cursor.fetchall()
                    if len(userData['menu'])>0:  
                        for item in userData['menu']:
                            if item['key']!='view':
                                if (item['level'] != 1) or (item.has_key('children') and len(item['children'])==0) or (not item.has_key('children')):
                                    print item
                                    return json.dumps(
                                        {
                                            "status": "fail",
                                            "msg": "用户权限分配有误"
                                        }
                                    )
                    # 获取权限信息
                    g.cursor.execute(
                        """select distinct c.read_only_state as is_readonly, d.group_key
                                        from sys_usergroup_role a
                                        left join sys_role b on a.role_id = b.role_id
                                        left join sys_role_function c on c.role_id = b.role_id
                                        left join sys_function_group d on d.group_id = c.group_id
                                        where a.user_group_id in (select user_group_id 
                                                                from sys_login_group 
                                                                where login_id = %s
                                                                ) 
                                        and b.state = '1' and d.state = '1' """, (userData['user']['login_id'],))

                    userData['readOnly'] = set()
                    userData['function'] = set()

                    for i in g.cursor.fetchall():
                        if int(i['is_readonly']) == 0:
                            userData['function'].add(i['group_key'])
                        userData['readOnly'].add(i['group_key'])

                    # 特殊权限处理
                    if "warnings" in userData["function"]:
                        userData["function"].add("important")
                        userData["function"].add("website")
                        userData["function"].add("dailytask")
                    if "unit" in userData["function"]:
                        userData["function"].add("safeEvents")

                    # 消重
                    userData['readOnly'] = list(userData['readOnly'])
                    userData['function'] = list(userData['function'])

                    # 保存登录信息
                    userData['token'] = session.sid
                    userData['project'] = {"name": config.PROJECT_NAME}
                    session['_currUser'] = userData
                    # 记录日志信息
                    auth.logsync(userData, {
                        "function": "权限系统",
                        "type": "登录",
                        "remark": "登录系统"
                    })
                    # 记录日志信息
                    auth.logsync(userData, {
                        "function": "权限系统",
                        "type": "登录",
                        "remark": "登录系统"
                    })
                    return json.dumps({"status": "success", "data": userData})
        else:
            return json.dumps({"status": "fail", "msg": "用户名或密码不正确"})
    except Exception, e:
        return json.dumps({"status": "fail", "msg": "用户登录失败",'s':str(e)})


@app.route('/api/auth/update', methods=['POST'])
@auth.permission('system')
def update_authinfo(_currUser):
    """修改密码"""
    form = ValidateForm(
        oldPwd=[
            '旧密码',
            V.required(type='base64'),
            V.rangelength(6, 20),
            V.pattern(r'^[a-zA-Z0-9_~`!@#\$%\^&\*\(\)\-\+,\.]+$',
                      '由字母、数字、符号(_~`!@#$%^&*()-+,.)组成')
        ],
        newPwd=[
            '新密码',
            V.required(type='base64'),
            V.rangelength(6, 20),
            V.pattern(r'^[a-zA-Z0-9_~`!@#\$%\^&\*\(\)\-\+,\.]+$',
                      '由字母、数字、符号(_~`!@#$%^&*()-+,.)组成')
        ],
        retPwd=[
            '确认新密码',
            V.required(type='base64'),
            V.rangelength(6, 20),
            V.equalto('newPwd')
        ])
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)
    try:
        g.cursor.execute(
            """select count(1) as num from sys_login where user_password = %s and login_id = %s""",
            (funcs.calc_md5(data['oldPwd']), _currUser['user']['login_id']))
        if g.cursor.fetchone()['num'] == 0:
            return json.dumps({"status": "fail", "msg": "旧密码不正确"})
        g.cursor.execute(
            """update sys_login set user_password = %s where login_id = %s returning user_name""",
            (funcs.calc_md5(data['newPwd']), _currUser['user']['login_id']))
        user_name = g.cursor.fetchone()['user_name']
        g.conn.commit()
        # 记录日志信息
        auth.logsync(_currUser, {
            "function": "权限系统",
            "type": "修改",
            "remark": "系统用户（%s）修改密码" % user_name
        })
        return json.dumps({"status": "success"})
    except Exception, e:
        return json.dumps({"status": "fail", "msg": "修改密码失败"})


@app.route('/api/auth/loginout', methods=['POST'])
@app.route('/api/auth/loginout/<token>', methods=['POST'])
def remove_authinfo(token=''):
    """用户注销"""
    try:
        token = token if token else request.values.get('token', '')
        token = token if token else request.headers.get('token', '')
        if token:
            g.cursor.execute("""delete from sys_session where token=%s""",
                             (token, ))
            g.conn.commit()
        else:
            session.pop('_currUser', None)
        return json.dumps({"status": "success"})
    except Exception, e:
        return json.dumps({"status": "fail", "msg": "注销失败"})


# 查询角色名称
@app.route('/api/auth/roles', methods=["POST"])
@auth.permission('role', "readOnly")
def roles_list():
    try:
        tb = table()
        g.cursor.execute("""select count(1) as num from sys_role a
                            left join sys_person b on a.r_person = b.person_id
                            %s """ % (tb.where(['a.role_name'], """a.state = '1' and a.role_id != 'location_0'""")))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""select a.r_person as ar_person, CASE when b.person_name is NULL
                                               THEN '系统默认'
                                               ELSE b.person_name
                                               end AS bperson_name,
                            a.role_name as arole_name, a.role_id as arole_id, 
                            d2t(a.update_time) as update_time, CASE when 
                                                                    a.data_source = '0' 
                                                                    then '门户子平台同步' 
                                                                    else '人工创建' end as data_source
                            from sys_role a
                            left join sys_person b 
                            on a.r_person = b.person_id %s %s %s %s""" %
                         (tb.where(['a.role_name'],
                                   """a.state = '1' and a.role_id != 'location_0'"""),
                          tb.orderBy(), tb.offset(), tb.limit()))
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "pageSize": int(tb.limit()[6:]),
                "total": total,
                "dataSource": g.cursor.fetchall()
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '查询失败'})


# 查询角色对应的菜单信息
@app.route('/api/auth/roles/group', methods=["POST"])
@auth.permission('role', "readOnly")
def roles_group():
    form = ValidateForm(role_id=['角色编号', V.required()])
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)
    result = []
    g.cursor.execute(
        """select d.cn_name as label, d.group_id as id, c.is_readonly, d.group_key
                        from sys_function_group d LEFT JOIN (
                        select a.group_id, a.role_id, a.read_only_state as is_readonly, b.cn_name
                        from sys_role_function a
                        LEFT JOIN sys_function_group b on a.group_id = b.group_id
                        where a.role_id = '%s' and b.state = '1') c on c.cn_name = d.cn_name
                        left join sys_function_group e on d.father_id = e.group_id
                        where d.level = 1 and role_id is not null order by d.step"""
        % data["role_id"])
    level_one_all = g.cursor.fetchall()
    for level_one in level_one_all:
        level_one["children"] = []
        g.cursor.execute(
            """select d.cn_name as label, d.group_id as id, c.is_readonly, d.group_key
                        from sys_function_group d LEFT JOIN (
                        select a.group_id, a.role_id, a.read_only_state as is_readonly, b.cn_name, b.group_key
                        from sys_role_function a
                        LEFT JOIN sys_function_group b on a.group_id = b.group_id
                        where a.role_id ='%s' and b.state = '1') c on c.cn_name = d.cn_name
                        left join sys_function_group e on d.father_id = e.group_id
                        where d.level = 2 and role_id is not null
                        and e.group_key  = '%s' order by d.step""" %
            (data["role_id"], level_one["group_key"]))
        level_two_all = g.cursor.fetchall()
        for level_two in level_two_all:
            g.cursor.execute(
                """select d.cn_name as label, d.group_id as id, c.is_readonly, d.group_key
                            from sys_function_group d LEFT JOIN (
                            select a.group_id, a.role_id, a.read_only_state as is_readonly, b.cn_name, b.group_key
                            from sys_role_function a
                            LEFT JOIN sys_function_group b on a.group_id = b.group_id
                            where a.role_id ='%s' and b.state = '1') c on c.cn_name = d.cn_name
                            left join sys_function_group e on d.father_id = e.group_id
                            where d.level = 3 and role_id is not null
                            and e.group_key  = '%s' order by d.step""" %
                (data["role_id"], level_two["group_key"]))
            level_two["children"] = g.cursor.fetchall()
            if len(level_two["children"]) == 0:
                level_two.pop("children")
            level_one["children"].append(level_two)
        result.append(level_one)
    return json.dumps({"status": "success", "data": result})


# 新增/修改角色  获取角色对应权限列表 及 功能页对应id
@app.route('/api/auth/roles/add', methods=["POST"])
@auth.permission('role')
def roles_add():
    form = ValidateForm(role_id=['角色编号', V.optional()])
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)

    try:
        # 获取 具有权限的 group_id 数组
        if data["role_id"]:
            g.cursor.execute("""select a.group_id from sys_role_function a
                                left join sys_function_group b on a.group_id = b.group_id
                                where role_id = '%s' and b.path is not NULL and b.state = '1'"""
                             % data["role_id"])
            group_ids = [item["group_id"] for item in g.cursor.fetchall()]

            g.cursor.execute(
                """select role_name from sys_role where role_id = '%s'""" %
                data["role_id"])
            data["role_name"] = g.cursor.fetchone()["role_name"]
        else:
            data["role_name"] = None
            group_ids = []

        # 获取 全部菜单结构
        result = []
        g.cursor.execute("""select group_id as id, group_key, cn_name as label
                            from sys_function_group where state = '1' and level = 1"""
                         )
        level_one_all = g.cursor.fetchall()
        for level_one in level_one_all:
            level_one["children"] = []
            g.cursor.execute(
                """select group_id as id, group_key, cn_name as label
                                from sys_function_group
                                where state = '1' and level = 2 and father_id = '%s' """
                % level_one["id"])
            level_two_all = g.cursor.fetchall()

            for level_two in level_two_all:
                g.cursor.execute(
                    """select group_id as id, group_key, cn_name as label
                                    from sys_function_group
                                    where state = '1' and level = 3 and father_id = '%s' """
                    % level_two["id"])
                level_two["children"] = g.cursor.fetchall()
                if len(level_two["children"]) == 0:
                    level_two.pop("children")
                level_one["children"].append(level_two)
            if len(level_one["children"]) == 0:
                level_one.pop("children")
            result.append(level_one)
        return json.dumps({
            "status": "success",
            "data": {
                "group_ids": group_ids,
                "result": result,
                "role_name": data["role_name"],
                "role_id": data["role_id"]
            }
        })
    except Exception:
        return json.dumps({"status": "fail", "msg": "操作角色信息失败"})


# 新增/修改角色 修改的菜单信息
@app.route('/api/auth/roles/update', methods=["POST"])
@auth.permission('role')
def roles_update(_currUser):
    form = ValidateForm(
        role_id=['角色编号', V.optional()],
        role_name=['角色名称', V.required()],
        group_ids=['菜单项编号', V.required()])
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)

    try:
        if data["role_id"]:
            _type = "修改"
            g.cursor.execute(
                """update sys_role set role_name = %s, r_person = %s, update_time = now(), data_source = '1'
                                where role_id = %s""",
                (data["role_name"], _currUser["user"]["person_id"], data["role_id"]))
            g.conn.commit()
        else:
            _type = "新增"
            g.cursor.execute("""select get_location_seq('sys_role') as id""")
            data["role_id"] = g.cursor.fetchone()["id"]
            g.cursor.execute(
                """insert into sys_role(role_id, role_name, data_source, r_person, update_time)
                                VALUES (%s, %s, '1', %s, now())""", (data["role_id"], data["role_name"], _currUser["user"]["person_id"]))
            g.conn.commit()

        g.cursor.execute(
            """delete from sys_role_function where role_id = '%s'""" % data["role_id"])

        for group_id in json.loads(data["group_ids"]):
            info = {
                "group_id": group_id,
                "role_id": data["role_id"],
                "is_readonly": '0'
            }
            g.cursor.execute(
                """insert into sys_role_function(group_id, role_id, read_only_state, data_source)
                                VALUES (%(group_id)s, %(role_id)s, %(is_readonly)s, '1')
                                ON conflict(group_id, role_id)
                                DO UPDATE SET read_only_state = %(is_readonly)s""", info)
        g.conn.commit()
        if len(json.loads(data["group_ids"])) != 0:
            g.cursor.execute("""select string_agg(cn_name,',') as cn_name
                                    from sys_function_group
                                    where group_id in (select unnest(array%s))""" % data["group_ids"].replace('"', '\''))

            cn_names = g.cursor.fetchone()["cn_name"]
        else:
            cn_names = ""

        rem = {
            "角色编号": data["role_id"],
            "角色名称": data["role_name"],
            "角色与功能对应列表": json.dumps(cn_names, ensure_ascii=False)
        }
        auth.logsync(
            _currUser, {
                "function": "权限管理",
                "type": _type,
                "remark": _type + "角色信息:" + record.recodelog(rem)
            })
        return json.dumps({
            "status": "success",
            "data": {
                "role_id": data["role_id"],
                "role_name": data["role_name"]
            }
        })
    except Exception, e:
        return json.dumps({"status": "fail", "msg": "操作角色信息失败"})


# 删除角色
@app.route('/api/auth/roles/delete', methods=["POST"])
@auth.permission('role')
def roles_delete(_currUser):
    form = ValidateForm(role_id=['角色编号', V.required()])
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)

    try:
        g.cursor.execute(
            """select role_name from sys_role WHERE role_id = '%s'""" %
            data["role_id"])

        role_name = g.cursor.fetchone()["role_name"]

        g.cursor.execute(
            """update sys_role set state = '0' WHERE role_id = '%s'""" %
            data["role_id"])
        g.conn.commit()

        rem = {"角色名称": role_name}
        auth.logsync(
            _currUser, {
                "function": "权限管理",
                "type": "删除",
                "remark": "删除角色:" + record.recodelog(rem)
            })

        return json.dumps({"status": "success"})
    except Exception:
        return json.dumps({"status": "fail", "msg": "删除角色失败"})


# 获取主题
@app.route('/api/getTheme', methods=['POST'])
@auth.permission('system', "readOnly")
def get_theme(_currUser):
    try:
        g.cursor.execute(
            """select fixed_footer,head_navigation,left_navigation,theme_colour from sys_user_theme WHERE person_id ='%s'"""
            % _currUser['user']['person_id'])
        data = g.cursor.fetchone()
        g.conn.commit()
        return json.dumps({"status": "success", "data": data})
    except Exception, e:
        return json.dumps({
            "status": "except",
            "msg": "获取主题失败"
        })


# 是否固定头部导航
@app.route('/api/ifFixedHeader', methods=['POST'])
@auth.permission('system', "readOnly")
def fixed_header(_currUser):
    try:
        form = ValidateForm(val=['是否固定头部导航', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """UPDATE sys_user_theme SET head_navigation = '%s' WHERE person_id ='%s'"""
            % (data['val'], _currUser['user']['person_id']))
        g.conn.commit()
        return json.dumps({"status": "success"})
    except Exception, e:
        return json.dumps({
            "status": "except",
            "msg": "修改头部导航栏失败"
        })


# 是否固定左侧导航
@app.route('/api/ifFixedLeftMenu', methods=['POST'])
@auth.permission('system', "readOnly")
def fixed_leftMenu(_currUser):
    try:
        form = ValidateForm(val=['是否左侧导航', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """UPDATE sys_user_theme SET left_navigation = '%s' WHERE person_id ='%s'"""
            % (data['val'], _currUser['user']['person_id']))
        g.conn.commit()
        return json.dumps({"status": "success"})
    except Exception, e:
        return json.dumps({
            "status": "except",
            "msg": "修改左侧导航栏失败"
        })


# 是否固定页脚
@app.route('/api/ifFixedFooter', methods=['POST'])
@auth.permission('system', "readOnly")
def fixed_footer(_currUser):
    try:
        form = ValidateForm(val=['是否固定页脚', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """UPDATE sys_user_theme SET fixed_footer = '%s' WHERE person_id ='%s'"""
            % (data['val'], _currUser['user']['person_id']))
        g.conn.commit()
        return json.dumps({"status": "success"})
    except Exception, e:
        return json.dumps({
            "status": "except",
            "msg": "修改页脚失败"
        })


# 修改主题
@app.route('/api/changeTheme', methods=['POST'])
@auth.permission('system', "readOnly")
def change_theme(_currUser):
    try:
        form = ValidateForm(val=['主题颜色', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """UPDATE sys_user_theme SET theme_colour = '%s' WHERE person_id ='%s'"""
            % (data['val'], _currUser['user']['person_id']))
        g.conn.commit()
        return json.dumps({"status": "success"})
    except Exception, e:
        return json.dumps({
            "status": "except",
            "msg": "修改主题颜色失败"
        })


# 获取登录地址
@app.route('/api/get/login/address', methods=['GET'])
def get_login_address():
    try:
        return json.dumps({"status": "success", "data": config.LOGIN_ADD.replace('\'', '')})
    except Exception:
        return json.dumps({
            "status": "except",
            "msg": "获取登录地址失败"
        })
